package com.website.travle.mapper;

import com.website.travle.pojo.Spot;
import com.website.travle.utils.JdbcUtils;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class ISpotDAOImpl implements ISpotDAO {
    @Override
    public int add(Spot spot) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("insert into spot values (null,?,?,?,?)");
        preparedStatement.setString(1, spot.getName());
        preparedStatement.setString(2, spot.getIntroduction());
        preparedStatement.setString(3, spot.getImageUrl());
        preparedStatement.setString(4, String.valueOf(spot.getCreateTime()));
        preparedStatement.execute();
        return 1;
    }

    @Override
    public int update(Spot spot) throws SQLException {
        return JdbcUtils.executeUpdate("update spot set name=?,introduction=?,image_url=?,create_time=? where id=?", spot.getName(), spot.getIntroduction(), spot.getImageUrl(), spot.getCreateTime(), spot.getId());
    }

    @Override
    public int delete(int id) throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("delete from spot where id=?");
        preparedStatement.setInt(1, id);
        boolean execute = preparedStatement.execute();
        return execute ? 1 : 0;
    }

    @Override
    public List<Spot> pageByLikeName(Integer page, Integer limit, String name) throws SQLException {
        int currentPage = page; // 当前页码
        int pageSize = limit; // 每页显示的记录数
        int offset = (currentPage - 1) * pageSize; // 查询结果的偏移量

        ResultSet resultSet = null;
        if (name == null) {
            resultSet = JdbcUtils.executeQuery("select * from spot limit ?,?", offset, pageSize);
        } else {
            name = "%" + name + "%";
            resultSet = JdbcUtils.executeQuery("select * from spot where name like ? limit ?,?", name, offset, pageSize);
        }
        ArrayList<Spot> spots = new ArrayList<>();
        while (resultSet.next()) {
            Spot spot = new Spot();
            spot.setId(resultSet.getInt("id"));
            spot.setName(resultSet.getString("name"));
            spot.setIntroduction(resultSet.getString("introduction"));
            spot.setImageUrl(resultSet.getString("image_url"));
            spot.setCreateTime(resultSet.getString("create_time"));
            spots.add(spot);
        } return spots;

    }

    @Override
    public int getTotal() throws SQLException {
        Connection connection = JdbcUtils.getConnection();
        PreparedStatement preparedStatement = connection.prepareStatement("select count(*) from spot");
        ResultSet resultSet = preparedStatement.executeQuery();
        while (resultSet.next()) {
            return resultSet.getInt(1);
        }
        return 0;
    }

    public static void main(String[] args) throws Exception {
        System.out.println(new Date().toString());
//        ISpotDAOImpl iSpotDAO = new ISpotDAOImpl();
////        iSpotDAO.add(new Spot(null, "a", "a", "a", new Date()));
//        iSpotDAO.update(new Spot(2, "b", "b", "b", new Date().toString()));
//        iSpotDAO.delete(1);
//        List<Spot> spots = iSpotDAO.pageByLikeName(2, 3, "a");
//        for (Spot spot : spots) {
//            System.out.println(spot);
//        }
    }
}
